Data wrangling - Deficit as % of GDP

Preparing data

Uploading data

df_deficit_pct <- read_csv("data/deficit_pct_gdp_OECD.csv")

Creating a wide-form dataset with columns for each year There are 23 rows with flag E (estimate/preliminary data) and 1 B (break in series) that were put in table df_e_or_b

df_deficit_pct %>% count(`Flag Codes`)

## # A tibble: 3 x 2
##   `Flag Codes`     n
##   <chr>        <int>
## 1 B                1
## 2 E               23
## 3 <NA>          1116

df_deficit_pct %>% filter(!is.na(`Flag Codes`))

## # A tibble: 24 x 8
##    LOCATION INDICATOR SUBJECT MEASURE FREQUENCY  TIME deficit_pc `Flag Codes`
##    <chr>    <chr>     <chr>   <chr>   <chr>     <dbl>      <dbl> <chr>       
##  1 FIN      GGNLEND   TOT     PC_GDP  A          1975       4.93 E           
##  2 FIN      GGNLEND   TOT     PC_GDP  A          1976       7.61 E           
##  3 FIN      GGNLEND   TOT     PC_GDP  A          1977       6.25 E           
##  4 FIN      GGNLEND   TOT     PC_GDP  A          1978       3.83 E           
##  5 FIN      GGNLEND   TOT     PC_GDP  A          1979       3.40 E           
##  6 FRA      GGNLEND   TOT     PC_GDP  A          2018      -2.29 E           
##  7 FRA      GGNLEND   TOT     PC_GDP  A          2019      -3.01 E           
##  8 GRC      GGNLEND   TOT     PC_GDP  A          2010     -11.3  B           
##  9 GRC      GGNLEND   TOT     PC_GDP  A          2018       1.02 E           
## 10 GRC      GGNLEND   TOT     PC_GDP  A          2019       1.52 E           
## # … with 14 more rows

df_wide_deficit <- df_deficit_pct %>% 
  select(-`Flag Codes`) %>% #this removes the variable Flag Codes to make the wide dataset cleaner
  pivot_wider(names_from = TIME, values_from = deficit_pc)

Creating a comparators variable

comparators <- c("DNK","IRL","NZL","NOR","SWE")
    df_deficit_pct <- df_deficit_pct %>%
    mutate(in_comparators=if_else(LOCATION%in%comparators,TRUE,FALSE)) 

Creating rolling averages over 5, 7 and 10 years

  df_deficit_pct <- df_deficit_pct %>% #this function uses longform
  group_by(LOCATION) %>% 
  mutate(bal_05ya = rollmean(deficit_pc, k = 5, fill = NA),
         bal_07ya = rollmean(deficit_pc, k = 7, fill = NA),
         bal_10ya = rollmean(deficit_pc, k = 10, fill = NA)) %>% 
        ungroup() #this closes that group_by(LOCATION)

Creating different measures of rolling minimum deficits

Creating rolling minimums over 3 and 5 years This is the minimum deficit in rolling 5 year periods including GFC

df_deficit_pct <- df_deficit_pct %>% #this function uses longform
  group_by(LOCATION) %>% 
  mutate(min_05y = rollapply(deficit_pc,5,min, fill=NA, align = "right"),
         min_03y = rollapply(deficit_pc,3,min, fill=NA, align = "right")
         ) %>% 
  ungroup ()

Creating rolling minimums over 3 and 5 years This is the minimum deficit in rolling 5 year periods excluding GFC

df_deficit_pct <- df_deficit_pct %>% #this function uses longform
  group_by(LOCATION) %>% 
  filter(TIME != 2009 | TIME != 2010 | TIME != 2011 | TIME != 2012 | TIME != 2013) %>%
  mutate(min_05y_no_GFC = rollapply(deficit_pc,5,min, fill=NA, align = "right"),
         min_03y_no_GFC = rollapply(deficit_pc,3,min, fill=NA, align = "right")
         ) %>% 
  ungroup ()

Creating maximum values of 5-rolling minimum deficits

max_min_5 <- df_deficit_pct %>% 
  group_by(LOCATION) %>% 
  summarise(max_5 = max(min_05y, na.rm=TRUE))

Creating maximum values of 5-rolling minimums without GFC years but using rolling minimum levels that included GFC years when calculated

max_min_5_no_GFC <- df_deficit_pct %>% 
  group_by(LOCATION) %>%
  filter(TIME != 2009 | TIME != 2010 | TIME != 2011 | TIME != 2012 | TIME != 2013) %>% 
  summarise(max_5_GFC = max(min_05y, na.rm=TRUE))

Creating maximum values of 5-rolling minimums without GFC years using rolling minimum levels that did not take the GFC years into account when calculated

max_min_5_no_GFC_2 <- df_deficit_pct %>% 
  group_by(LOCATION) %>%
  summarise(max_5_GFC_2 = max(min_05y_no_GFC, na.rm=TRUE))

Exploring data

deficit_pc and its rolling average recodings: Positive values mean budget surpluses and negative deficits

Plot 1

Visualising balance as % of GDP for all countries over the years (positive values are surpluses, negative values are deficits)

  p1 <- df_deficit_pct %>% #making p into a ggplot object to visualise w/ plotly
 # line below would invert surplus/deficit in deficit_pc, making higher values mean higher deficits
 # mutate(deficit_pc=-deficit_pc) %>% 
  ggplot(aes(TIME, deficit_pc, colour=LOCATION)) + 
  geom_line()
  ggplotly(p1) #plotly lets me see the legend when scrolling through the plot

Plot 2

Visualising 5 year rolling averages of balance as % of GDP for all countries

    p2 <- df_deficit_pct %>%
    ggplot(aes(TIME, bal_05ya, colour=LOCATION)) + 
    geom_line()
    ggplotly(p2)

Plot 3

Visualising 7 year rolling averages of balance as % of GDP for all countries

    p3 <- df_deficit_pct %>%
    ggplot(aes(TIME, bal_07ya, colour=LOCATION)) + 
    geom_line()
    ggplotly(p3)

Plot 4

Visualising 10 year rolling averages of balance as % of GDP for all countries

    p4 <- df_deficit_pct %>%
    ggplot(aes(TIME, bal_10ya, colour=LOCATION)) + 
    geom_line()
    ggplotly(p4) 

Plot 5

Visualising 7 year rolling averages of balance as % of GDP for all countries from 2000

    p5 <- df_deficit_pct %>%
    filter(TIME>=2000) %>% 
    ggplot(aes(TIME, bal_07ya, colour=LOCATION)) + 
    geom_line()
    ggplotly(p5)

Plot 6

Visualising 7 year rolling averages of deficit % of GDP for all countries higher values mean higher deficit

    p6 <- df_deficit_pct %>%
    ggplot(aes(TIME, -bal_07ya, colour=LOCATION)) + 
    geom_line()
    ggplotly(p6)

###Plot 7 Visualising 7 year rolling averages of deficit % of GDP for all countries with highlighted comparators higher values mean higher deficit

    p7 <- df_deficit_pct %>%
    ggplot(aes(TIME, -bal_07ya, colour=in_comparators, group=LOCATION)) + 
    geom_line()
    ggplotly(p7)

Plot 8

Visualising deficit as % of GDP for all countries with highlighted comparators higher values mean higher deficit

    p8 <- df_deficit_pct %>%
    ggplot(aes(TIME, -deficit_pc, colour=in_comparators, group=LOCATION)) + 
    geom_line()
    ggplotly(p8)

Thoughts (04/05/2021)

• Using the whole timeline gives us Finland during its economic crisis which might be interesting for the point that countries borrow more during crises
• The shorter timeline (starting at around 2000) would be more parsimonious
• We can choose between 5, 7 or 10 rolling averages. I think 7 might be the fairer picture.
• Rolling averages make us lose data points for the more recent years where it can’t be calculated like that. It would be good to think about whether that’s a tradeoff that we’re happy with.
• General story seems to be that no one has run the kind of deficit that Scotland might be looking at consistently and particularly not outside of crisis periods.
• Interesting that Norway is the country Scotland is most often compared with and it’s a total outlier in terms of running surpluses.
• Ireland, another potential comparison, is on the other end of that.
• This group of comparators I chose (“DNK”,“IRL”,“NZL”,“NOR”,“SWE”) might not be the best one, I picked them because they’re some of the comparators that come up more often. Might make more sense to stick to the countries that the Sustainable Growth Commission uses as their case studies.
• I think the best way to visualise this in the actual paper would probably be this kind of chart (obviously with very different formatting and less crowded) or if we want to just stick to the comparators we could do are bar charts (the ones that have the change graph at their tip)

Plot 9

Visualising maximum minimum deficits over 5 years including GFC years (haven’t figured out a way to keep the year information)

The highest minimum deficit including the financial crisis was Greece -5.65

    p9 <- max_min_5 %>%
    ggplot(aes(LOCATION,max_5)) + 
    geom_col()
    ggplotly(p9)

Plot 10

Visualising maximum minimum deficits over 5 years excluding GFC years (haven’t figured out a way to keep the year information)

The rolling minimum was calculated including the GFC years

    p10 <- max_min_5_no_GFC %>%
    ggplot(aes(LOCATION,max_5_GFC)) + 
    geom_col()
    ggplotly(p10)

Plot 11

Visualising maximum minimum deficits over 5 years excluding GFC (haven’t figured out a way to keep the year information)

The rolling minimum was calculated excluding the GFC years

    p11 <- max_min_5_no_GFC_2 %>%
    ggplot(aes(LOCATION,max_5_GFC_2)) + 
    geom_col()
    ggplotly(p11)